# coding: UTF-8
# Filename : report_reg.py
# Company : imedia
# Author : rose
# -*-coding:utf-8 -*-
'对应report_reg.php文件逻辑  挖掘入库stat_vistor_signup'

import sys
import utils, log, mysql

#日期设定
yesterday = str(utils.getYesterday())
if len(sys.argv) >= 2:
    yesterday = sys.argv[1]
    
print yesterday

del_sql = '''delete from stat_vistor_signup where `date` = %s'''


ok = mysql.delete(del_sql, (yesterday))

search_sql = '''select tb1.cus_id, count(tb1.id) pv, count(distinct tb1.ip) uv, 
count(tb1.id)/count(distinct tb1.ip) as browser from im_log tb1, customer tb2
where tb1.cus_id = tb2.id
and tb2.type = 1 
and tb1.`time` >= %s
and tb1.`time` <= %s
group by tb1.cus_id'''


signup_count_sql = '''
SELECT  u.cus_id, count(*) as regcount   
FROM `edu_student`  as u
where u.in_time >= %s
and  u.in_time <= %s
group by u.cus_id
'''
insert_sql = '''insert  into `stat_vistor_signup`
(`cus_id`, `date`, `pv`, `uv`, `browser`, `sign_up`, `rate`) 
values (%s, %s, %s, %s, %s, 0, 0)'''

update_sql = '''update stat_vistor_signup set sign_up = %s, rate = %s
 where cus_id = %s
 and `date` = %s'''

start_time_str = yesterday + ' 00:00:00'
end_time_str = yesterday + ' 23:59:59'
format = '%Y-%m-%d %H:%M:%S'
start_time = utils.strToTimestamp(start_time_str, format)
end_time = utils.strToTimestamp(end_time_str, format)

#用户uv数据结构封装 用于计算报名率
dict = {}
rst = mysql.doSearch(search_sql, [start_time, end_time])
#pv,uv,browser,cus_id入库逻辑
for obj in rst:
    dict[obj[0]] = obj[2]
    param = (obj[0], yesterday, obj[1], obj[2], obj[3])
    mysql.insert(insert_sql, param)

#报名数 报名率挖掘更新
rst = mysql.doSearch(signup_count_sql, [start_time, end_time])
for obj in rst:
    if dict.has_key(obj[0]):
        uv = dict[obj[0]]
        sign_count = obj[1]
        sign_rate = (sign_count/uv) * 100
        mysql.update(update_sql, [sign_count, str(sign_rate), obj[0], yesterday])

log.info('stat_vistor_signup ' + yesterday + ' 数据挖掘成功')
